package db;

import java.io.UnsupportedEncodingException;
import java.math.BigInteger;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;

import model.UserAccount;

public class UserAccountDAO {
	private static final String TABLE = "user_account";	
	private static String onSqlError = "SQL statement fault";
	private static MessageDigest md5;
	
	static{
		try {
			md5 = MessageDigest.getInstance("MD5");
		} catch (NoSuchAlgorithmException e) {			
			e.printStackTrace();
		}
	}
	
	private static final String 
		ID_FIELD = "user_account",		
		SUBJECT_TYPE_FIELD = "subject_type_fk",
		SUBJECT_FIELD = "subject_fk",
		USERNAME_FIELD = "username",
		PASSWORD_FIELD = "passw",
		STATUS_FIELD = "status",
		VALID_FROM_FIELD = "valid_from",
		VALID_TO_FIELD = "valid_to",
		CREATED_BY_FIELD = "created_by",
		CREATED_FIELD = "created",
		PASSWORD_NEVER_EXPIRES_FIELD = "password_never_expires";
	
	private static final String INSERT_STATEMENT_STRING = 
			"INSERT INTO "+TABLE+" " 
			+"("+SUBJECT_TYPE_FIELD+", "+SUBJECT_FIELD+", "+USERNAME_FIELD+", "+PASSWORD_FIELD+", " 
			+STATUS_FIELD+", "+VALID_FROM_FIELD+", "
			+VALID_TO_FIELD+", "+CREATED_BY_FIELD+", "+CREATED_FIELD+", "+PASSWORD_NEVER_EXPIRES_FIELD+") " +
			"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
	
	private static final String UPDATE_STATEMENT_STRING = 
			"UPDATE "+TABLE+" SET " 
			+SUBJECT_TYPE_FIELD+" = ?, "+SUBJECT_FIELD+" = ?, "+USERNAME_FIELD+" = ?, "+PASSWORD_FIELD+" = ?, " 
			+STATUS_FIELD+" = ?, "+VALID_FROM_FIELD+" = ?, "
			+VALID_TO_FIELD+" = ?, "+CREATED_BY_FIELD+" = ?, "+CREATED_FIELD+" = ?, "+PASSWORD_NEVER_EXPIRES_FIELD+" = ? " 
			+"WHERE "+ID_FIELD+" = ?";
	
	private static final String DELETE_STATEMENT_STRING =
			"DELETE FROM "+TABLE+" WHERE "+ID_FIELD+" = ?";
	
	private static final String USERNAME_EXISTS_STATEMENT_STRING =
			"SELECT * FROM "+TABLE+" WHERE lower("+USERNAME_FIELD+") = lower(?)";
	
	private static final String FIND_STATEMENT_STRING =
			"SELECT * FROM "+TABLE+" WHERE "+PASSWORD_FIELD+" = ? AND lower("+USERNAME_FIELD+") = lower(?)";
	
	private static PreparedStatement insertStatement, updateStatement, deleteStatement, usernameExistsStatement, findStatement;
	
	static{
		try {
			insertStatement = ImbiConnection.getConnection().prepareStatement(INSERT_STATEMENT_STRING);
			updateStatement = ImbiConnection.getConnection().prepareStatement(UPDATE_STATEMENT_STRING);
			deleteStatement = ImbiConnection.getConnection().prepareStatement(DELETE_STATEMENT_STRING);
			usernameExistsStatement = ImbiConnection.getConnection().prepareStatement(USERNAME_EXISTS_STATEMENT_STRING);
			findStatement = ImbiConnection.getConnection().prepareStatement(FIND_STATEMENT_STRING);
		} catch (SQLException e) {					
			System.exit(0);
		}
	}
	
	public UserAccount find(int id) {			
		try{				
			Statement st = ImbiConnection.getConnection().createStatement();
			st.executeQuery("SELECT * FROM "+TABLE+" WHERE "+ID_FIELD+" = "+id);
			UserAccount user = extractAccount(st.getResultSet()).get(0);			
			return user;
		} catch (SQLException | IndexOutOfBoundsException s){
				System.out.println(onSqlError);				
				return null;
		}		
	}
	
	/**
	 * 
	 * @param name The user name
	 * @param password The password as plain string, not MD5
	 * @return the User account or null
	 */
	public UserAccount find(String name, String password){				
		if(password == null || name == null) return null;
		password = md5(password);			
		try{				
			findStatement.setString(1, password); 
			findStatement.setString(2, name);
			findStatement.execute();		    
			UserAccount user = extractAccount(findStatement.getResultSet()).get(0);			
			return user;
		} catch (SQLException | IndexOutOfBoundsException s){
				System.out.println(onSqlError);				
				return null;
		}		
		
	}

	private String md5(String password) {
		try {
			md5.update(password.getBytes("UTF-8"));
		} catch (UnsupportedEncodingException e) {			
			e.printStackTrace();
		}		
		password = new BigInteger(1, md5.digest()).toString(16);
		return password;
	}
	
	public boolean exists(String username) {			
		try{							
			usernameExistsStatement.setString(1, username); 			
			usernameExistsStatement.execute();		    					
			return extractAccount(usernameExistsStatement.getResultSet()).size() > 0; 
		} catch (SQLException s){
				System.out.println(onSqlError);				
				return false;
		}				
	}
	
	public ArrayList<UserAccount> findAll() {
		try{				
			Statement st = ImbiConnection.getConnection().createStatement();
			st.executeQuery("SELECT * FROM "+TABLE);
			return extractAccount(st.getResultSet());		
			
		} catch (SQLException s){
				System.out.println(onSqlError);				
				return null;
		}	
	}	
	
	private ArrayList<UserAccount> extractAccount(ResultSet r) throws SQLException {					
		ArrayList<UserAccount> list = new ArrayList<UserAccount>();
		while(r.next()){		
			Date validFrom = r.getDate(7) != null ? new Date(r.getDate(7).getTime()) : null;
			Date validTo = r.getDate(8) != null ? new Date(r.getDate(8).getTime()) : null;
			Date created = r.getTimestamp(10) != null ? new Date(r.getTimestamp(10).getTime()) : null;
			boolean passwordNeverExpires = "Y".equalsIgnoreCase(r.getString(11));
			
			UserAccount user = new UserAccount(
				r.getInt(1),
				r.getInt(2),
				r.getInt(3),
				r.getString(4),
				r.getString(5),
				r.getInt(6),
				validFrom,
				validTo,
				r.getInt(9),
				created,
				passwordNeverExpires
			);
			list.add(user);
		}
		return list;
	}
		
	public void insert(UserAccount u){
		if(exists(u.getUsername())) throw new IllegalArgumentException("User name already exists!");
		if(!isValidMD5(u.getPassword())){
			u.setPassword(md5(u.getPassword()));
		}
		try{
			composeStatement(insertStatement, u);	
		    insertStatement.executeUpdate();			
		} catch (SQLException s){
				System.out.println(onSqlError);									
		}		
	}
	
	public void update(UserAccount u){
		if(!isValidMD5(u.getPassword())){
			u.setPassword(md5(u.getPassword()));
		}
		try{										
			composeStatement(updateStatement, u);	       
		    updateStatement.setInt(11, u.getId());
		    updateStatement.executeUpdate();			
		} catch (SQLException s){
				System.out.println(onSqlError);								
		}			
	}
	
	private void composeStatement(PreparedStatement statement, UserAccount u) throws SQLException{
		java.sql.Date validFrom = u.getValidFrom() != null ? new java.sql.Date(u.getValidFrom().getTime()) : null;
		java.sql.Date validTo = u.getValidTo() != null ? new java.sql.Date(u.getValidTo().getTime()) : null;
		Timestamp created = 
				u.getCreated() != null ? new Timestamp(u.getCreated().getTime()) : null;
		
		statement.setInt(1, u.getSubjectType());
	    statement.setInt(2, u.getSubject());
	    statement.setString(3, u.getUsername());
	    statement.setString(4, u.getPassword());
	    statement.setInt(5, u.getStatus());
	    statement.setDate(6, validFrom);
	    statement.setDate(7, validTo);
	    statement.setInt(8, u.getCreatedBy());	      
	    statement.setTimestamp(9, created);		
	    statement.setString(10, u.isPasswordNeverExpires() ? "Y":"N");
	}
	
	public void delete(UserAccount u){
		try{										
		    deleteStatement.setInt(1, u.getId());		    
		    deleteStatement.executeUpdate();			
		} catch (SQLException s){
				System.out.println(onSqlError);						
		}			
	}
	
	private boolean isValidMD5(String s) {
	    return s.matches("[a-fA-F0-9]{32}");
	}

	public ArrayList<UserAccount> findByEmployee(int id) {
		try{				
			Statement st = ImbiConnection.getConnection().createStatement();
			st.executeQuery("SELECT * FROM "+TABLE+" WHERE "+SUBJECT_FIELD+" = "+id);
			return extractAccount(st.getResultSet());					
		} catch (SQLException s){
				System.out.println(onSqlError);				
				return null;
		}	
	}
	
}
